from __future__ import with_statement # for reading files using 'with' statement

# ********* NOTE **********
# It turns out that ArcGIS doesn't really like the file as it is at the
# moment. I can't find a way to attach spatial data to each row. So I've
# made a new script projection_to_multiple_gis.py that creates a
# separate csv file for each variable (e.g. Age0-4, Births etc). These
# can be joined to the spatial data and then transposed using 'Transpose
# Fields'.
# ********* END NOTE **********

# Turn the results of a projection -- which show, for each district, the number
# of people of different age groups in each projected year -- into a format that
# is easy for a GIS to understand. E.g.:
#
#   Area, Year, TotalPeople, Age0-4, Age5-9, ..., Births, Deaths ...
#   00DA, 2034, 1500, 1600, 3100, ....
#   ...
#
# The input files are produced by pop_projection and are called something like
#   summary_table_<scenario>_disaggregate.csv
#
# Although the inputs also disaggregate by males and females, these are
# disregarded here.
#

__author__="nick"
__date__ ="$Apr 24, 2012 9:25:31 AM$"

import sys

if len(sys.argv) != 2:
    print "Usage: python projection_to_csv.py infile.csv"

infile = sys.argv[1]
outfile = infile[:-4]+"_gis.csv"
print "Reading file", infile
print "Writing to file", outfile

with open(infile,'r') as inf, open(outfile, 'w') as outf:

    linecount = 0 # Count total number of lines
    reg_lc = 24 # Count number of lines read from current region
    region = "" # The name of the region we're reading
    years = [] # Years to transpose

    outf.write("Region,Variable,Year,Value\n") # Header line

    for line in inf:

        if linecount == 0:
            # First line will be Region, 2004, .., 2100. Find the years to transpose
            linesplit = line.split(',')[1:] # (first item is 'Region', ignore it)
            # Males,Females,People for each year, so only choose every third item
            for a in linesplit[0::3]:
                years.append(a.strip())
            linecount += 1
            continue

        linesplit = line.split(',')

        if reg_lc == 24: # Have read all the data for this region, start a new one
            reg_lc = 0
            region = linesplit[0]
            print "REGION:",region

        else: # Read some info for this region
            variable = linesplit[0] # Name of the variable (e.g. Age0-4)
            variable = variable.replace("+","pl") # Get rid of '+' symbol
            # Check varialble names don't start with numbers, this confuses the GIS
            try:
                int(variable[0])
                variable = ("a"+variable) # append a character to the variable name
            except ValueError:
                pass # Don't need to do anything, the variable doesn't start with a number

            # Collect every third value (count for all persons), ignoring the first item (the
            # variable name) and the last one (a newline character)
            values = linesplit[1:-1][0::3] 

            assert len(years)>=len(values), "Must be more years than values:" \
                    +str(len(years))+" , "+str(len(values))

            for val,yr in zip(values, years):
                outf.write("%(region)s,%(variable)s,%(yr)s,%(val)s \n" % \
                        {'region':region, 'variable':variable, 'yr':yr, 'val':val} )
                #print "%(region)s,%(variable)s,%(yr)s,%(val)s \n" % \
                #        {'region':region, 'variable':variable, 'yr':yr, 'val':val}





        reg_lc += 1

        linecount += 1

print "Finished"
